#!/use/bin/python
# coding=utf-8
# 将  大数据统计的数据 保存到数据库中
import datetime
import pymysql
import requests
import json

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    try:
        conn, cur = getConfConnection()
        #print(sql)
        cur.execute(sql)
        numResult = cur.fetchone()
        num = 0
        if numResult is not None:
            num = numResult[0]
        if num is None:
            return 0
        return num
    finally:
        cur.close()
        conn.close()

# 使用节点曝光的次数 计算转化率
def saveNodeConversionRate(date):
    conn, cur = getConfConnection()
    try:
        delsql = f" delete from inter_actify_r_conversion_rate where ref_date= '{date}' "
        cur.execute(delsql)
        conn.commit()
        #记录 每个节点的曝光次数
        insertSql = f""" INSERT into inter_actify_r_conversion_rate(node_id,current_node_num,version_id,data_type,ref_date) select n.node_id,n.pv, t.version_id,1,'{date}' from 
                (select * from   inter_actify_r_node_number tt  where tt.ref_date = '{date}' and tt.pv != 0) n left join inter_actify_nodes  t   on t.node_id = n.node_id""";
        cur.execute(insertSql)
        conn.commit()
        insertCount = selectNumBySql(f"select count(1) from inter_actify_r_conversion_rate t where t.ref_date = '{date}'")
        logDetail = f"{date}日-保存从大数据获取的节点曝光的次数,插入的节点条数为:{insertCount}"
        print(logDetail)
    finally:
        cur.close()
        conn.close()

#更新开始节点曝光次数
def saveRootNode(date):
    conn, cur = getConfConnection()
    try:
        # 获取 开始节点的曝光次数
        selectRootNum = f""" select * from ( select n.pv,t.version_id from  (  select * from   inter_actify_r_node_number tt  where tt.ref_date = '{date}' and tt.pv != 0 ) n left join
        (select *  from inter_actify_nodes n1 where n1.parent_node_id = 'root' ) t   on t.node_id = n.node_id ) ttt  where version_id is not null """
        #print(f'selectRootNum:{selectRootNum}')
        cur.execute(selectRootNum)
        rows = cur.fetchall()
        rowCount = len(rows)
        # 循环处理查询结果
        for row in rows:
            pv = row[0]
            versionId = row[1]

            if pv is not None and pv != 0 and versionId is not None and versionId != 0:
                print( "version_id: ", versionId,"PV: ", pv)
                updateSql = f"""  update inter_actify_r_conversion_rate t set t.root_node_num = {pv} where t.node_id in 
                (select n1.node_id from  inter_actify_nodes n1 where n1.version_id = '{versionId}') and t.ref_date = '{date}'"""
                cur.execute(updateSql)
                conn.commit()
        logDetail = f"{date}日-更新开始节点曝光次数,开始节点的条数为:{rowCount}"
        print(logDetail)
    finally:
        cur.close()
        conn.close()


def saveParentNode(date):
    conn, cur = getConfConnection()
    try:
        # 将节点 及 查询出来放入map中
        query = f"select t.pv, t.node_id from inter_actify_r_node_number t where t.ref_date = '{date}' and t.pv != 0"
        cur.execute(query)
        rows = cur.fetchall()

        # 创建空字典
        resultMap = {}

        # 循环处理查询结果，将结果放入字典中
        for row in rows:
            pv = row[0]
            nodeId = row[1]
            resultMap[nodeId] = pv

        # 查询每个节点 的父节点
        selectNodeNum = f""" select pv,node_id,version_id,parent_node_id,node_type from (
            select n.uv,n.pv,n.node_id, t.version_id,t.parent_node_id,(select p.node_type from inter_actify_nodes p where p.node_id =  t.parent_node_id) node_type 
            from (select * from   inter_actify_r_node_number tt  where tt.ref_date = '{date}' and tt.pv != 0) n left join (select *  from inter_actify_nodes n1 where n1.parent_node_id <> 'root' )  t   on t.node_id = n.node_id) 
            ttt where ttt.version_id is not null """
        cur.execute(selectNodeNum)
        #print(f'selectNodeNum:{selectNodeNum}')
        rows = cur.fetchall()
        rowCount = len(rows)
        # 循环处理查询结果
        for row in rows:
            pNodeIdPv = 0
            pv = row[0]
            nodeId = row[1]
            versionId = row[2]
            pNodeId = row[3]
            nodeType = row[4]

            if nodeType == 2 :
                pNodeIdPv = resultMap.get(pNodeId, 0)

            elif nodeType == 3:
                nodeType,pNodeId = getParent(pNodeId)
                pNodeIdPv = resultMap.get(pNodeId, 0)
                print(f'nodeId:{nodeId},ppNodeId:{pNodeId},pNodeIdPv:{pNodeIdPv}')


            #更新上一个节点数量
            updatePNodeSql = f""" update inter_actify_r_conversion_rate t set t.previous_node_num = {pNodeIdPv},t.last_update_time = now() where t.node_id = '{nodeId}' and t.ref_date = '{date}'  """
            #print(f'updatePNodeNum:{updatePNodeSql}')
            cur.execute(updatePNodeSql)
            conn.commit()
        logDetail = f"{date}日-更新父级节点曝光次数,更新节点的条数为:{rowCount}"
        print(logDetail)
    finally:
        cur.close()
        conn.close()

def getParent(nodeId):
    selectSql = "select t.node_type, t.parent_node_id, t.node_id from inter_actify_nodes t where t.node_id = %s "
    conn, cur = getConfConnection()
    try:
        cur.execute(selectSql, (nodeId,))
        row = cur.fetchone()
        nodeType = row[0]
        pNodeId = row[1]
        nodeId = row[2]

        # 判断节点类型是否为3(判定节点) 如果是继续查询，直到节点类型不为3
        while nodeType == 3 and pNodeId:
            nodeType, nodeId = getParent(pNodeId)

        return nodeType, nodeId

    finally:
        cur.close()
        conn.close()

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

if __name__ == '__main__':
    today = getdate(1)
    saveNodeConversionRate(today)
    saveRootNode(today)
    saveParentNode(today)
    #getButtonNum(today)

